﻿Imports System.Data.SqlClient
Imports System.Data



''' <summary>
''' Class provides all methods to interact with DB
''' </summary>
''' <remarks></remarks>
Public Class DataProvider
    Private strConnect As String = "Server=" ' "Server=THEVO-PC\SQLEXPRESS;Database=TIMEKEEPER;User Id=yubiuser;Password=yubipass;"
    Private sqlConnect As SqlConnection


    Public Sub New(ByVal serverName As String)
        strConnect &= serverName & "\SQLEXPRESS2008;Database=TIMEKEEPER;User Id=yubiuser;Password=yubipass;"
        sqlConnect = New SqlConnection()
        sqlConnect.ConnectionString = strConnect
    End Sub

    Protected Function Update(ByVal storedProcedureName As String, ByVal paramName As String(), ByVal paramValue As Object()) As Integer

        Try
            sqlConnect.Open()
            Dim sqlCom As SqlCommand
            sqlCom = New SqlCommand()
            sqlCom.Connection = sqlConnect
            sqlCom.CommandType = CommandType.StoredProcedure
            sqlCom.CommandText = storedProcedureName
            AddSQLParameter(sqlCom, paramName, paramValue)
            Return sqlCom.ExecuteNonQuery()

        Catch ex As ApplicationException
            System.Console.WriteLine(ex.Message)
            MsgBox(ex.Message, MsgBoxStyle.OkOnly, "Message")
        Catch ex As Exception
            System.Console.WriteLine(ex.Message)
            MsgBox(ex.Message, MsgBoxStyle.OkOnly, "Message")
        End Try
        Return 0
    End Function


    Protected Function Query(ByVal storedProcedureName As String) As DataTable
        Dim dt As DataTable = New DataTable()
        Try
            sqlConnect.Open()
            Dim sqlCom As SqlCommand = New SqlCommand()
            sqlCom.Connection = sqlConnect
            sqlCom.CommandType = CommandType.StoredProcedure
            sqlCom.CommandText = storedProcedureName
            Dim dataAdapter As SqlDataAdapter = New SqlDataAdapter(sqlCom)
            dataAdapter.Fill(dt)
            Return dt
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.OkOnly, "Message")
        End Try
        Return dt
    End Function

    Protected Function Query(ByVal storedProcedureName As String, ByVal paramName As String(), ByVal paramValue As Object()) As DataTable
        Dim dt As DataTable = New DataTable()
        Try
            sqlConnect.Open()
            Dim sqlCommand As SqlCommand = New SqlCommand()
            sqlCommand.Connection = sqlConnect
            sqlCommand.CommandType = CommandType.StoredProcedure
            sqlCommand.CommandText = storedProcedureName
            AddSQLParameter(sqlCommand, paramName, paramValue)
            Dim dataAdapter As SqlDataAdapter = New SqlDataAdapter(sqlCommand)
            dataAdapter.Fill(dt)
            Return dt
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.OkOnly, "Message")
        End Try
        Return dt
    End Function

    'this sub closes connection to the DB server
    Protected Sub Close()
        If (sqlConnect Is Nothing) <> True Then
            sqlConnect.Close()
        End If
    End Sub

    Private Sub AddSQLParameter(ByRef sqlCommand As SqlCommand, ByVal paramName As String(), ByVal paramValue As Object())

        If (paramName Is Nothing) <> True Then
            Dim param(paramName.Length - 1) As SqlParameter
            For i As Integer = 0 To paramName.Length - 1
                param(i) = New SqlParameter(paramName(i), paramValue(i))
                Console.WriteLine(paramValue(i))
            Next
            sqlCommand.Parameters.AddRange(param)
        End If

    End Sub
End Class
